using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Common;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.ETL
{
    /// <summary>
    /// 
    /// </summary>
    public partial class SyncDbTableEditor : Form, IComponentEditor 
    {
        private object _OwnerActivityNode;
        private SyncDbTableComponent _SyncTbl;

        /// <summary>
        /// 
        /// </summary>
        public SyncDbTableEditor()
        {
            InitializeComponent();
        }

        private void SyncDbTableEditor_Load(object sender, EventArgs e)
        {
            this.PopulateDbProviders();
        }

        private void PopulateDbProviders()
        {
            this.cbo_SrcDbProvider.Items.Clear();
            this.cbo_TgtDbProvider.Items.Clear();
            for(int i=0;i<=2;i++)
            {
                DataProviderType dbProvider = (DataProviderType) i;
                this.cbo_SrcDbProvider.Items.Add(dbProvider.ToString());
                this.cbo_TgtDbProvider.Items.Add(dbProvider.ToString());
            }
        }

        public event UpdateComponentSettings UpdateComponentSettingEvent;

        public object OwnerActivityNode
        {
            get { return _OwnerActivityNode; }
            set { _OwnerActivityNode = value; }
        }

        public Type WorkflowComponentType
        {
            get { return typeof(SyncDbTableComponent); }
        }

        public void LoadSettings(
            GlobalVariable[] globalVars,
            WorkflowComponentBase[] fromComponents, 
            object activityNode, WorkflowComponentBase wfComponent)
        {
            this.cbo_SrcTbls.SelectedIndex = -1;
            this.cbo_SrcTbls.Text = string.Empty;
            this.cbo_SrcTbls.Items.Clear();
            this.cbo_SrcTimeStampField.Items.Clear();
            this.cbo_SrcTimeStampField.SelectedIndex = -1;
            this.cbo_SrcTimeStampField.Text = string.Empty;
            this.clb_SrcUniqueKeys.Items.Clear();

            this._OwnerActivityNode = activityNode;
            this._SyncTbl = (SyncDbTableComponent) wfComponent;

            this.cbo_SrcDbProvider.SelectedIndex = (int) this._SyncTbl.SrcDbProvider;
            this.txt_SrcConnStr.Text = this._SyncTbl.SrcConnStr;

            this.txt_SrcSchema.Text = this._SyncTbl.SrcSchema;
            if (!string.IsNullOrEmpty(this._SyncTbl.SrcConnStr))
            {
                // this.btn_RefreshSrcTables_Click(this, new EventArgs());
                if (this.cbo_SrcTbls.Items.Count > 0)
                {
                    for (int i = 0; i < this.cbo_SrcTbls.Items.Count; i++)
                    {
                        if (this.cbo_SrcTbls.Items[i].ToString() == this._SyncTbl.SrcTblName)
                        {
                            this.cbo_SrcTbls.SelectedIndex = i;
                            break;
                        }
                    }
                }
                else
                {
                    this.cbo_SrcTbls.Text = this._SyncTbl.SrcTblName;
                }

                if ((!string.IsNullOrEmpty(this._SyncTbl.SrcTimeStampField)) && this.cbo_SrcTimeStampField.Items.Count>0)
                {
                    for(int i=0;i<this.cbo_SrcTimeStampField.Items.Count;i++)
                    {
                        if(this.cbo_SrcTimeStampField.Items[i].ToString()==this._SyncTbl.SrcTimeStampField)
                        {
                            this.cbo_SrcTimeStampField.SelectedIndex = i;
                            break;
                        }
                    }
                    this.cbo_SrcTimeStampField.SelectedText = this._SyncTbl.SrcTimeStampField;
                }
                else
                {
                    this.cbo_SrcTimeStampField.Text = this._SyncTbl.SrcTimeStampField;
                }
                if (this.clb_SrcUniqueKeys.Items.Count > 0 && this._SyncTbl.SrcTableUniqueKeys != null &&
                    this._SyncTbl.SrcTableUniqueKeys.Count > 0)
                {
                    for (int i = 0; i < this.clb_SrcUniqueKeys.Items.Count; i++)
                    {
                        if (this._SyncTbl.SrcTableUniqueKeys.Contains(this.clb_SrcUniqueKeys.Items[i].ToString()))
                        {
                            this.clb_SrcUniqueKeys.SetItemChecked(i, true);
                        }
                        else
                        {
                            this.clb_SrcUniqueKeys.SetItemChecked(i, false);
                        }
                    }
                }
                else if (this._SyncTbl.SrcTableUniqueKeys !=null && this._SyncTbl.SrcTableUniqueKeys.Count > 0)
                {
                    foreach(string uniqueKeyName in this._SyncTbl.SrcTableUniqueKeys)
                    {
                        this.clb_SrcUniqueKeys.Items.Add(uniqueKeyName);
                        this.clb_SrcUniqueKeys.SetItemChecked(this.clb_SrcUniqueKeys.Items.Count - 1, true);
                    }
                }
            }
            this.ck_UseSql.Checked = this._SyncTbl.UseSql;
            this.txt_SelectStatement.Text = this._SyncTbl.SelectStatement;

            this.cbo_TgtDbProvider.SelectedIndex = (int) this._SyncTbl.TgtDbProvider;
            this.txt_TgtConnStr.Text = this._SyncTbl.TgtConnStr;
            if (!string.IsNullOrEmpty(this._SyncTbl.TgtConnStr))
            {
                // this.btn_RefreshTgtTables_Click(this, new EventArgs());
                if (this.cbo_TgtTables.Items.Count > 0)
                {
                    for (int i = 0; i < this.cbo_TgtTables.Items.Count; i++)
                    {
                        if (this.cbo_TgtTables.Items[i].ToString() == this._SyncTbl.TgtTableName)
                        {
                            this.cbo_TgtTables.SelectedIndex = i;
                            break;
                        }
                    }
                }
                else
                {
                    this.cbo_TgtTables.Text = this._SyncTbl.TgtTableName;
                }
            }
            //if(this.cbo_SrcTbls.SelectedIndex >=0 && this.cbo_TgtTables.SelectedIndex>=0)
            //{
            //    this.btn_RefreshTgtTables_Click(this, new EventArgs());
            //}
        }

        private void btn_RefreshSrcTables_Click(object sender, EventArgs e)
        {
            try
            {
                DataProviderType dbProvider = (DataProviderType) this.cbo_SrcDbProvider.SelectedIndex;
                string connStr = this.txt_SrcConnStr.Text;
                DbSchemaCache.DbSchemaCacheKey key=new DbSchemaCache.DbSchemaCacheKey();
                key.DbProvider = dbProvider;
                key.SchemaName = this.txt_SrcSchema.Text;
                key.ConnStr = connStr;
                    if (this.ck_UseSql.Checked)
                        key.SelectStatement = this.txt_SelectStatement.Text;
                else
                        key.SelectStatement = string.Empty;

                DbSchemaCache schemaCache = new DbSchemaCache();
                if(this.ck_UseSql.Checked)
                {
                    List<TableColumn> cols = schemaCache.GetColumns(key, string.Empty, key.SelectStatement);
                    //TableColumn[] tblCols =
                    //    DbUtil.GetTableColumns(dbProvider, connStr, this.txt_SelectStatement.Text);
                    this.cbo_SrcTimeStampField.Items.Clear();
                    this.clb_SrcUniqueKeys.Items.Clear();
                    if (cols != null && cols.Count > 0)
                    {
                        foreach (TableColumn col in cols)
                        {
                            this.clb_SrcUniqueKeys.Items.Add(col.ColumnName);
                            this.cbo_SrcTimeStampField.Items.Add(col.ColumnName);
                        }
                    }
                }
                else
                {
                    // string schemaName = this.txt_SrcSchema.Text;
                    List<string> entityNames = schemaCache.GetTables(key);
                    // List<string> entityNames = this.GetEntityNamesForConnection(connStr, schemaName);
                    //if (entityNames == null || entityNames.Count == 0)
                    //{
                    //    entityNames = new List<string>();
                    //    string[] tblNames =
                    //        DbUtil.GetTableNames(dbProvider, this.txt_SrcSchema.Text, this.txt_SrcConnStr.Text);
                    //    this.cbo_SrcTbls.Items.Clear();
                    //    if (tblNames != null && tblNames.Length > 0)
                    //    {
                    //        foreach (string tblName in tblNames)
                    //        {
                    //            entityNames.Add(tblName);
                    //        }
                    //    }

                    //    string[] viewNames =
                    //        DbUtil.GetViewNames(dbProvider, this.txt_SrcSchema.Text, this.txt_SrcConnStr.Text);
                    //    if (viewNames != null && viewNames.Length > 0)
                    //    {
                    //        foreach (string viewName in viewNames)
                    //        {
                    //            entityNames.Add(viewName);
                    //        }
                    //    }

                    //    this.SaveEntityNamesForConnection(connStr, schemaName, entityNames);
                    //}
                    entityNames.Sort();
                    if (entityNames.Count > 0)
                    {
                        foreach (string entityName in entityNames)
                        {
                            this.cbo_SrcTbls.Items.Add(entityName);
                        }
                    }
                }
                
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void btn_RefreshTgtTables_Click(object sender, EventArgs e)
        {
            DataProviderType tgtProvider = (DataProviderType) this.cbo_TgtDbProvider.SelectedIndex;
            string connStr = this.txt_TgtConnStr.Text;
            DbSchemaCache.DbSchemaCacheKey key=new DbSchemaCache.DbSchemaCacheKey();
            key.ConnStr = connStr;
            key.DbProvider = tgtProvider;
            key.SchemaName = this.txt_SrcSchema.Text;
            key.SelectStatement = string.Empty;
            DbSchemaCache schemaCache=new DbSchemaCache();
            List<string> tblNames = schemaCache.GetTables(key);

            if (this.cbo_TgtTables.SelectedIndex < 0 && !string.IsNullOrEmpty(this.cbo_TgtTables.Text))
            {
                string tgtTableName = this.cbo_TgtTables.Text;
                this.SaveSettings();
                this._SyncTbl.TgtTableName = tgtTableName;
                try
                {
                    //SyncDbTableWorker worker=new SyncDbTableWorker();
                    //worker.EnsureTgtTableSchema(this._SyncTbl.SrcDbProvider,
                    //    this._SyncTbl.SrcConnStr,
                    //    this._SyncTbl.SrcSchema,
                    //    this._SyncTbl.SrcTblName,
                    //    this._SyncTbl.SelectStatement,
                    //    this._SyncTbl.TgtDbProvider,
                    //    this._SyncTbl.TgtConnStr,
                    //    this._SyncTbl.TgtTableName,
                    //    this._SyncTbl.UseSql);
                    // string[] tblNames = DbUtil.GetTables(this.txt_TgtConnStr.Text);
                    
                    this.cbo_TgtTables.Items.Clear();
                    if (tblNames != null && tblNames.Count > 0)
                    {
                        foreach (string tblName in tblNames)
                        {
                            this.cbo_TgtTables.Items.Add(tblName);
                            
                        }
                    }
                    if(this.cbo_TgtTables.Items.Count>0)
                    {
                        for(int i=0;i<this.cbo_TgtTables.Items.Count;i++)
                        {
                            if(this.cbo_TgtTables.Items[i].ToString()==tgtTableName)
                            {
                                this.cbo_TgtTables.SelectedIndex = i;
                                break;
                            }
                        }
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                // string[] tblNames = DbUtil.GetTables(this.txt_TgtConnStr.Text);
                this.cbo_TgtTables.Items.Clear();
                if (tblNames != null && tblNames.Count > 0)
                {
                    foreach (string tblName in tblNames)
                    {
                        this.cbo_TgtTables.Items.Add(tblName);
                    }
                }
            }
        }

        public TestResult Test()
        {
            SyncDbTableInstruction instruction = new SyncDbTableInstruction(
                this._SyncTbl.ComponentID, Guid.Empty,
                this._SyncTbl.SrcDbProvider,
                this._SyncTbl.SrcConnStr,
                this._SyncTbl.SrcSchema,
                this._SyncTbl.SrcTblName,
                this._SyncTbl.UseSql,
                this._SyncTbl.SelectStatement,
                this._SyncTbl.SrcTableUniqueKeys,
                this._SyncTbl.SrcTimeStampField,
                this._SyncTbl.TgtDbProvider,
                this._SyncTbl.TgtConnStr,
                this._SyncTbl.TgtTableName,
                this._SyncTbl.FieldMappings);
            SyncDbTableWorker worker = new SyncDbTableWorker();
            worker.SetInstruction(Guid.NewGuid(), instruction, string.Empty, this._SyncTbl.ComponentName);
            TestResult result=new TestResult(true, string.Empty);
            try
            {
                DataTable dtTest = worker.TestTableSync();
                if (dtTest != null && dtTest.Rows.Count > 0)
                {
                    StringBuilder buffer=new StringBuilder();
                    for(int i=0;i<dtTest.Rows.Count;i++)
                    {
                        for(int k=0;k<dtTest.Columns.Count;k++)
                        {
                            buffer.Append(dtTest.Rows[i][k] == null ? string.Empty : dtTest.Rows[i][k].ToString());
                            if(k<dtTest.Columns.Count-1)
                            {
                                buffer.Append(",");
                            }
                        }
                        buffer.Append(Environment.NewLine);
                    }
                    result.Message = buffer.ToString();
                }
                else
                {
                    result.Succeed = false;
                    result.Message = "Unable to get any data";
                }
            }
            catch(Exception ex)
            {
                result.Succeed = false;
                result.Message = ex.ToString();
            }
            return result;
        }

        public void SaveSettings()
        {
            this._SyncTbl.SrcDbProvider = (DataProviderType) this.cbo_SrcDbProvider.SelectedIndex;
            this._SyncTbl.SrcConnStr = this.txt_SrcConnStr.Text;
            this._SyncTbl.SrcSchema = this.txt_SrcSchema.Text;
            this._SyncTbl.SrcTblName = this.cbo_SrcTbls.Text;
            this._SyncTbl.UseSql = this.ck_UseSql.Checked;
            this._SyncTbl.SelectStatement = this.txt_SelectStatement.Text;
            this._SyncTbl.SrcTableUniqueKeys = null;
            if(this.clb_SrcUniqueKeys.Items.Count>0)
            {
                List<string> uniqueKeys=new List<string>();
                for(int i=0;i<this.clb_SrcUniqueKeys.Items.Count;i++)
                {
                    if(this.clb_SrcUniqueKeys.GetItemChecked(i))
                    {
                        uniqueKeys.Add(this.clb_SrcUniqueKeys.Items[i].ToString());
                    }
                }
                if(uniqueKeys.Count>0)
                {
                    this._SyncTbl.SrcTableUniqueKeys = uniqueKeys;
                }
            }
            if(string.IsNullOrEmpty(this.cbo_SrcTimeStampField.Text))
            {
                this._SyncTbl.SrcTimeStampField = string.Empty;
            }
            else
            {
                this._SyncTbl.SrcTimeStampField = this.cbo_SrcTimeStampField.Text;
            }

            this._SyncTbl.TgtDbProvider = (DataProviderType) this.cbo_TgtDbProvider.SelectedIndex;
            this._SyncTbl.TgtConnStr = this.txt_TgtConnStr.Text;
            this._SyncTbl.TgtTableName = this.cbo_TgtTables.Text;
            this._SyncTbl.FieldMappings=new Dictionary<string, string>();
            foreach(DataGridViewRow dr in this.dv_FieldMappings.Rows)
            {
                if((!dr.IsNewRow) && (dr.Cells[0].Value !=null) && (dr.Cells[1].Value !=null))
                {
                    string srcFieldName = dr.Cells[0].Value.ToString();
                    string tgtFieldName = dr.Cells[1].Value.ToString();
                    if((!string.IsNullOrEmpty(srcFieldName)) && (!string.IsNullOrEmpty(tgtFieldName)))
                    {
                        this._SyncTbl.FieldMappings.Add(srcFieldName,tgtFieldName);
                    }
                }
            }
        }

        private void btn_OK_Click(object sender, EventArgs e)
        {
            this.SaveSettings();

            if(this.UpdateComponentSettingEvent !=null)
            {
                this.UpdateComponentSettingEvent(this._OwnerActivityNode, this._SyncTbl);
            }

            this.Close();
        }

        private void btn_Cancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void cbo_SrcTbls_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cbo_SrcTbls.SelectedIndex >= 0)
            {
                DataProviderType dbProvider = (DataProviderType) this.cbo_SrcDbProvider.SelectedIndex;
                DbSchemaCache.DbSchemaCacheKey key=new DbSchemaCache.DbSchemaCacheKey();
                key.DbProvider = dbProvider;
                key.ConnStr = this.txt_SrcConnStr.Text;
                key.SelectStatement = string.Empty;
                key.SchemaName = this.txt_SrcSchema.Text;
                key.TableName = this.cbo_SrcTbls.SelectedItem.ToString();

                if(this.ck_UseSql.Checked)
                {
                    key.SelectStatement = this.txt_SelectStatement.Text;
                }
                DbSchemaCache schemaCache=new DbSchemaCache();
                List<TableColumn> cols =
                    schemaCache.GetColumns(key, this.cbo_SrcTbls.SelectedItem.ToString(), key.SelectStatement);

                //TableColumn[] tblCols =
                //    DbUtil.GetTableColumns(dbProvider, this.txt_SrcConnStr.Text, this.txt_SrcSchema.Text,
                //                           this.cbo_SrcTbls.SelectedItem.ToString());
                this.cbo_SrcTimeStampField.Items.Clear();
                this.clb_SrcUniqueKeys.Items.Clear();
                if (cols != null && cols.Count > 0)
                {
                    foreach (TableColumn col in cols)
                    {
                        this.clb_SrcUniqueKeys.Items.Add(col.ColumnName);
                        this.cbo_SrcTimeStampField.Items.Add(col.ColumnName);
                    }
                }
            }
        }

        private void InitFieldMappings()
        {
            this.dv_FieldMappings.Rows.Clear();

            if (this.cbo_TgtTables.SelectedIndex >= 0 &&
                (this.cbo_SrcTbls.SelectedIndex >= 0 || this.txt_SelectStatement.Text.Length > 0))
            {
                DataProviderType srcDbProvider = (DataProviderType) this.cbo_SrcDbProvider.SelectedIndex;
                DataProviderType tgtDbprovider = (DataProviderType) this.cbo_TgtDbProvider.SelectedIndex;
                DbSchemaCache.DbSchemaCacheKey srcKey = new DbSchemaCache.DbSchemaCacheKey();
                srcKey.DbProvider = srcDbProvider;
                srcKey.ConnStr = this.txt_SrcConnStr.Text;
                srcKey.SchemaName = this.txt_SrcSchema.Text;
                srcKey.SelectStatement = this.ck_UseSql.Checked ? this.txt_SelectStatement.Text : string.Empty;
                DbSchemaCache.DbSchemaCacheKey tgtKey = new DbSchemaCache.DbSchemaCacheKey();
                tgtKey.DbProvider = tgtDbprovider;
                tgtKey.ConnStr = this.txt_TgtConnStr.Text;
                tgtKey.SchemaName = this.txt_SrcSchema.Text;
                tgtKey.SelectStatement = string.Empty;
                string srcTableName = string.Empty;
                if(this.cbo_SrcTbls.SelectedIndex>=0 && (!this.ck_UseSql.Checked))
                {
                    srcTableName = this.cbo_SrcTbls.SelectedItem.ToString();
                }
                DbSchemaCache schemaCache=new DbSchemaCache();
                List<TableColumn> srcCols =
                    schemaCache.GetColumns(srcKey, srcTableName, srcKey.SelectStatement);
                List<TableColumn> tgtCols =
                    schemaCache.GetColumns(tgtKey, this.cbo_TgtTables.SelectedItem.ToString(), string.Empty);

                //TableColumn[] srcCols = null;
                //if (this.ck_UseSql.Checked)
                //{
                //    srcCols =
                //        DbUtil.GetTableColumns(srcDbProvider, this.txt_SrcConnStr.Text, this.txt_SelectStatement.Text);
                //}
                //else
                //{
                //    srcCols = DbUtil.GetTableColumns(srcDbProvider, this.txt_SrcConnStr.Text, this.txt_SrcSchema.Text,
                //                                     this.cbo_SrcTbls.SelectedItem.ToString());
                //}
                //TableColumn[] tgtCols =
                //    DbUtil.GetTableColumns(tgtDbprovider, this.txt_TgtConnStr.Text, string.Empty,
                //                           this.cbo_TgtTables.SelectedItem.ToString());
                
                if (srcCols != null && srcCols.Count > 0)
                {
                    foreach (TableColumn srcCol in srcCols)
                    {
                        DataGridViewRow dr = new DataGridViewRow();
                        DataGridViewTextBoxCell cellSrc = new DataGridViewTextBoxCell();
                        cellSrc.Value = srcCol.ColumnName;
                        dr.Cells.Add(cellSrc);

                        DataGridViewComboBoxCell cellTgt = new DataGridViewComboBoxCell();
                        if (tgtCols != null && tgtCols.Count > 0)
                        {
                            foreach (TableColumn tgtCol in tgtCols)
                            {
                                cellTgt.Items.Add(tgtCol.ColumnName);
                                if (tgtCol.ColumnName.ToLower() == srcCol.ColumnName.ToLower())
                                {
                                    cellTgt.Value = srcCol.ColumnName;
                                }
                            }
                        }
                        dr.Cells.Add(cellTgt);

                        this.dv_FieldMappings.Rows.Add(dr);
                    }
                }

                this.dv_FieldMappings.Refresh();
            }
        }

        

        private void cbo_TgtTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.InitFieldMappings();
        }

        private void btn_Test_Click(object sender, EventArgs e)
        {
            this.SaveSettings();
            TestResult result = this.Test();
            MessageBox.Show(result.Message, result.Succeed.ToString());
        }

        void worker_WorkerErredEvent(Guid ticketID, Type workerType, InstructionBase instructionBase, string error)
        {
            MessageBox.Show(error);
        }

        void worker_WorkerFinishedEvent(Guid ticketID, Type workerType, InstructionBase instructionBase, ResultBase resultBase)
        {
            MessageBox.Show("Done");
        }

        private void ck_UseSql_CheckedChanged(object sender, EventArgs e)
        {
            if(this.ck_UseSql.Checked)
            {
                this.cbo_SrcTbls.Enabled = false;
                this.txt_SrcSchema.Enabled = false;
                this.txt_SelectStatement.Enabled = true;
            }
            else
            {
                this.cbo_SrcTbls.Enabled = true;
                this.txt_SrcSchema.Enabled = true;
                this.txt_SelectStatement.Enabled = false;
            }
        }
    }
}